package com.fsoft.core.excel.service.impl;

import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Semaphore;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.codec.binary.Base64;
import org.apache.commons.lang3.StringUtils;
import org.jxls.common.Context;
import org.jxls.expression.JexlExpressionEvaluator;
import org.jxls.transform.Transformer;
import org.jxls.util.JxlsHelper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.fsoft.core.Global;
import com.fsoft.core.common.base.entity.UserVo;
import com.fsoft.core.context.FSContext;
import com.fsoft.core.excel.entity.ExcelModel;
import com.fsoft.core.excel.service.ExcelService;
import com.fsoft.core.utils.DateTimeUtils;
import com.fsoft.core.utils.FileManager;
import com.fsoft.core.utils.OgnlUtils;
import com.fsoft.core.utils.excel.CustomFunctions;
import com.fsoft.core.utils.excel.ExcelContext;

@Service
@Transactional(rollbackFor = Exception.class)
public class ExcelServiceImpl implements ExcelService {

	private static Logger log = LoggerFactory.getLogger(ExcelServiceImpl.class);
	@Autowired
	NamedParameterJdbcOperations jdbcTemplate;
	/**
	 * 控制同时下载的信号量 设置最大20个用户
	 */
	final Semaphore semaphore = new Semaphore(20);

	@Override
	public void executeExport(ExcelModel model, HttpServletRequest request, HttpServletResponse response) throws Exception {
		semaphore.acquire();
		try {
			long beginTime = System.currentTimeMillis();
			String outputFileTitle = model.getFileName();
			String modelName = model.getModelPath();
			// 读取 Excel 模版的输入流
			InputStream templateStream = new FileInputStream(FileManager.getExcelTemplateRealPath() + modelName + ".xls");
			// 获取响应下载的输出流
			OutputStream targetStream = getExcelOS(outputFileTitle, request, response, ".xls");

			// 创建 Jxls 容器
			Context context = new Context();
			// 传递行数据
			context.putVar("data", getExcelRows(model, context));
			// 传递单位名称
			UserVo user = FSContext.getInstance().getUser();
			String titleName = StringUtils.isBlank(model.getFiletitle()) ? user.getOrgName() + model.getFileName() : model.getFiletitle();
			context.putVar("title", titleName);
			if (user != null) {
				context.putVar("orgName", user.getOrgName());
				context.putVar(Global.F_USERID, user.getId());
				context.putVar("userName", user.getName());
				context.putVar("deptName", user.getDeptName());
			}
			// 传递当前日期
			context.putVar("currDate", DateTimeUtils.formatToday());
			// 执行导出
			doExport(templateStream, targetStream, context);
			long endTime = System.currentTimeMillis();
			log.info("运行耗时：" + (endTime - beginTime) + " ms");
		} finally {
			semaphore.release();
		}
	}

	@SuppressWarnings("unchecked")
	private List<Map<String, Object>> getExcelRows(ExcelModel model, Context context) {
		// 如果外界传来数据源，直接获取并填充
		if (model.getList() != null) {
			return (List<Map<String, Object>>) model.getList();
		}
		return null;

	}

	private void doExport(InputStream templateStream, OutputStream targetStream, Context context) throws IOException {
		// 获取 JxlsHelper 实例
		JxlsHelper jxlsHelper = JxlsHelper.getInstance();

		Transformer transformer = jxlsHelper.createTransformer(templateStream, targetStream);
		// transformer.getCellData(cellRef)
		JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig().getExpressionEvaluator();
		// jx 标签功能扩展
		Map<String, Object> map = new HashMap<String, Object>(1);
		map.put(CustomFunctions.CUSTOM_TAG_NAME, new CustomFunctions());
		evaluator.getJexlEngine().setFunctions(map);
		// 根据模版生成 Excel 文件
		jxlsHelper.processTemplate(context, transformer);

	}

	private OutputStream getExcelOS(String outputFileTitle, HttpServletRequest request, HttpServletResponse response, String filetype) throws IOException {
		// 文件名称拼接
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
		String currentDateStr = sdf.format(new Date());
		if (StringUtils.isNotBlank(outputFileTitle)) {
			outputFileTitle = outputFileTitle + filetype;
		} else {
			outputFileTitle = currentDateStr + filetype;
		}

		// 从请求头中获取用户代理信息
		String userAgent = request.getHeader("User-Agent");
		String firefoxName = "Firefox";
		if (userAgent.contains(firefoxName)) {
			// 如果浏览器为 Firefox，则使用 BASE64 编码
			outputFileTitle = "=?UTF-8?B?" + Base64.encodeBase64String(outputFileTitle.getBytes("UTF-8")) + "?=";
		} else {
			// 否则使用 UTF-8 编码
			outputFileTitle = URLEncoder.encode(outputFileTitle, "UTF-8");
		}

		// 设置文件名称
		response.setHeader("Content-Disposition", "attachment; filename=" + outputFileTitle);
		// 指定下载文件的类型
		response.setContentType("application/vnd.ms-excel");
		return response.getOutputStream();
	}

	@Override
	public void executeExportDef(ExcelModel model, HttpServletRequest request, HttpServletResponse response) throws Exception {
		semaphore.acquire();
		try {
			// 列集合
			List<String> clounmlist = new ArrayList<String>();
			// 数据集合
			List<List<Object>> dataList = new ArrayList<List<Object>>();
			// 列对应英文字段
			List<String> columnsList = new ArrayList<String>();
			// 获取传过来的列以及对应的字段名
			Map<String, String> map = model.getColumns();
			if (map == null) {
				return;
			}

			for (String key : map.keySet()) {
				clounmlist.add(key);
				columnsList.add(map.get(key));
			}

			String outputFileTitle = model.getFileName();
			String modelName = "ModelDefined";

			String filePath = FileManager.getExcelTemplateRealPath() + modelName + ".xls";
			// 创建 Jxls 容器
			ExcelContext context = new ExcelContext();
			context.putVar("headers", clounmlist);
			getRowData(columnsList, model, dataList);
			context.putVar("data", dataList);
			context.putVar("size", clounmlist.size());
			// 读取 Excel 模版的输入流
			InputStream templateStream = new FileInputStream(filePath);
			// 获取响应下载的输出流
			OutputStream targetStream = getExcelOS(outputFileTitle, request, response, ".xls");

			// 传递单位名称
			String orgName = FSContext.getInstance().getUser().getOrgName();
			//
			String titleName = StringUtils.isBlank(model.getFiletitle()) ? orgName + model.getFileName() : model.getFiletitle();
			context.putVar("title", titleName);
			// 传递单位名称
			UserVo user = FSContext.getInstance().getUser();
			if (user != null) {
				context.putVar("orgName", user.getOrgName());
				context.putVar(Global.F_USERID, user.getId());
				context.putVar("userName", user.getName());
				context.putVar("deptName", user.getDeptName());
			}
			// 传递当前日期
			context.putVar("currDate", DateTimeUtils.formatToday());
			// 执行导出
			doExport(templateStream, targetStream, context);

		} finally {
			semaphore.release();
		}
	}

	@SuppressWarnings("unchecked")
	private List<Map<String, Object>> getRowData(List<String> columnsList, ExcelModel model, List<List<Object>> dataList) throws Exception {
		List<Map<String, Object>> returnList = new ArrayList<Map<String, Object>>();
		List<Object> rowDataList = (List<Object>) model.getList();
		if (OgnlUtils.isNotEmpty(rowDataList)) {
			for (Object t : rowDataList) {
				Map<String, Object> map = null;
				if (t instanceof Map<?, ?>) {
					map = (Map<String, Object>) t;
				} else {
					map = new HashMap<String, Object>(16);
					java.beans.BeanInfo beanInfo = Introspector.getBeanInfo(t.getClass());
					PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
					for (PropertyDescriptor property : propertyDescriptors) {
						String key = property.getName();
						// 过滤class属性
						if (!"class".equals(key)) {
							// 得到property对应的getter方法
							java.lang.reflect.Method getter = property.getReadMethod();
							// 获取不到getter,给空值
							if (getter == null) {
								map.put(key, null);
								continue;
							}
							Object value = getter.invoke(t);
							if (null == value) {
								map.put(key, "");
							} else {
								map.put(key, value);
							}
						}
					}
				}
				List<Object> tempList = new ArrayList<Object>();
				for (String key : columnsList) {
					if (map.containsKey(key)) {
						tempList.add(map.get(key));
					} else {
						tempList.add(null);
					}
				}
				dataList.add(tempList);
				returnList.add(map);
			}
		}
		return returnList;
	}

//	static final String XLSSUFFIX = "xls";
//	static final String XLSXSUFFIX = "xlsx";
//	static final String STRINGSPLIT = "\\.";
//
//	public void executeImport(MultipartFile file) throws Exception {
//		if (file.isEmpty())
//			throw new Exception("选择的文件不存在，请重新选择!");
//		String fileName = file.getOriginalFilename().toLowerCase();
//		if (!(fileName.endsWith(XLSSUFFIX) || fileName.endsWith(XLSXSUFFIX)))
//			throw new Exception("请选择Excel文件");
//		List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
//		// 读取数据
//		DataInModel dataInModel = readExcelData(file, dataList);
//		if (dataList.size() == 0)
//			throw new Exception("Excel表内数据，请重新选择文件!");
//		// 校验数据
//		newdataList = checkExcelData(dataList, dataInModel, errorlist);
//		if (newdataList.size() == 0) {
//			return RetVo.getNewInstance(retstate.OK, null, null);
//		}
//		if (errorlist.size() > 0) {
//			return RetVo.getNewInstance(retstate.ERROR, StringUtils.join(errorlist, "<br>"), null);
//		}
//		// 保存数据
//		saveExceleData(newdataList, dataInModel);
//		return RetVo.getNewInstance(retstate.OK, null, null);
//	}
//
//	private DataInModel readExcelData(MultipartFile file, List<Map<String, Object>> dataList) throws Exception {
//		// 初始化jxls
//		JxlsHelper jxlsHelper = JxlsHelper.getInstance();
//		InputStream inputStream = file.getInputStream();
//		Transformer transformer = jxlsHelper.createTransformer(inputStream, null);
//		// 获取单位格
//		List<CellData> listCellDatas = transformer.getCommentedCells();
//		if (listCellDatas.size() == 0) {
//			throw new Exception("Excel表内无映射数据，请重新选择文件!");
//		}
//		// 第一个单元的批注
//		String headString = listCellDatas.get(0).getCellComment();
//		DataInModel dataInModel = JSON.parseObject(headString, DataInModel.class);
//		// 开始的行数，校验的需要注明是第几行
//		Integer startRow = listCellDatas.get(0).getCellRef().getRow();
//		dataInModel.setStartRow(startRow);
//		Workbook wb = null;
//		// 读取表格数据，jxls提供读取表格的方法需要配置xml，使用poi方法读取
//		if (file.getOriginalFilename().toLowerCase().endsWith(XLSSUFFIX)) {
//			wb = new HSSFWorkbook(file.getInputStream());
//		} else if (file.getOriginalFilename().toLowerCase().endsWith(XLSXSUFFIX)) {
//			wb = new XSSFWorkbook(file.getInputStream());
//		}
//		if (wb == null) {
//			throw new Exception("无法读取选择的文件，请选择2003版的excel文件");
//		}
//
//		org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(wb.getActiveSheetIndex());
//		if (sheet.getLastRowNum() == 0) {
//			wb.close();
//			throw new Exception("Excel表内无导入数据，请重新选择文件!");
//		}
//		// 总的单元格列数
//		int totalCells = sheet.getRow(startRow).getPhysicalNumberOfCells();
//		for (int i = startRow + 1; i <= sheet.getLastRowNum(); i++) {
//			Map<String, Object> dataMap = new HashMap<String, Object>(16);
//			for (int j = 0; j < totalCells; j++) {
//				Cell headRow = sheet.getRow(startRow).getCell(j);
//				// DataInModelColumn column =
//				// dataInModelColumnMap.get(headRow.getStringCellValue());
//				// 列对应的中文
//				String key = headRow.getStringCellValue();
//				Row row = sheet.getRow(i);
//				if (row == null) {
//					dataMap.put(key, null);
//					continue;
//				}
//				Cell cell = sheet.getRow(i).getCell(j);
//				// 为空无值
//				if (cell == null) {
//					dataMap.put(key, null);
//					continue;
//				}
//				int type = cell.getCellType();
//				if (type == HSSFCell.CELL_TYPE_STRING) {
//					dataMap.put(key, cell.getStringCellValue());
//				} else if (type == HSSFCell.CELL_TYPE_NUMERIC) {
//					if (HSSFDateUtil.isCellInternalDateFormatted(cell)) {
//						dataMap.put(key, cell.getDateCellValue());
//					} else {
//						cell.setCellType(CellType.STRING);
//						dataMap.put(key, cell.getStringCellValue());
//					}
//				} else if (type == HSSFCell.CELL_TYPE_BLANK) {
//					dataMap.put(key, null);
//				} else if (type == HSSFCell.CELL_TYPE_FORMULA) {
//					// 把公式转为string
//					cell.setCellType(CellType.STRING);
//					dataMap.put(key, cell.getStringCellValue());
//				}
//			}
//			dataList.add(dataMap);
//		}
//		wb.close();
//		return dataInModel;
//	}
}
